/*
 * Copyright (c) 2018-2999 广州市蓝海创新科技有限公司 All rights reserved.
 *
 * https://www.mall4j.com/
 *
 * 未经允许，不可做商业用途！
 *
 * 版权所有，侵权必究！
 */
package com.yami.shop.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.lang.Snowflake;
import cn.hutool.core.util.BooleanUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.JSONValidator;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.yami.shop.bean.enums.DeliveryTemplateType;
import com.yami.shop.bean.enums.ProdStatusEnums;
import com.yami.shop.bean.enums.ProdType;
import com.yami.shop.bean.enums.SigningStatus;
import com.yami.shop.bean.event.GetTransportNamesEvent;
import com.yami.shop.bean.model.*;
import com.yami.shop.bean.param.*;
import com.yami.shop.bean.vo.CategoryShopVO;
import com.yami.shop.common.bean.SysProdConfig;
import com.yami.shop.common.config.Constant;
import com.yami.shop.common.exception.YamiShopBindException;
import com.yami.shop.common.i18n.I18nMessage;
import com.yami.shop.common.i18n.LanguageEnum;
import com.yami.shop.common.util.Json;
import com.yami.shop.common.util.PageParam;
import com.yami.shop.common.util.PoiExcelUtil;
import com.yami.shop.dao.ProductMapper;
import com.yami.shop.service.*;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author yami
 */
@Service
@AllArgsConstructor
@Slf4j
@EnableAspectJAutoProxy(proxyTargetClass = true, exposeProxy = true)
public class ProductExcelServiceImpl implements ProductExcelService {

    private final ProductMapper productMapper;
    private final ProductService productService;
    private final CategoryService categoryService;
    private final ApplicationContext applicationContext;
    private final SkuService skuService;
    private final Snowflake snowflake;
    private final CategoryShopService categoryShopService;
    private final SysConfigService sysConfigService;
    private static final String SEMICOLON = ";";
    private static final String COLON = ":";
    private static final Integer VALID_NUM = 2;

    @Override
    public int writerProdToExcel(List<ProductExportParam> products, ExcelWriter writer, Long shopId, Integer row) {
        if (CollUtil.isEmpty(products)) {
            return row;
        }
        List<Long> prodIds = products.stream().map(ProductExportParam::getProdId).collect(Collectors.toList());
        List<ProductExportParam> productExportParams = productMapper.listProdsByProdParam(prodIds);
        Map<Long, ProductExportParam> prodMap = productExportParams.stream().collect(Collectors.toMap(ProductExportParam::getProdId, p -> p));
        for (ProductExportParam esProd : products) {
            ProductExportParam prod = prodMap.get(esProd.getProdId());
            if (Objects.isNull(prod)) {
                continue;
            }
            List<Sku> skuList = prod.getSkuList();
            int firstRow = row + 1;
            int lastRow = row + skuList.size();
            int col = -1;
            col = writerProdData(writer, shopId, prod, esProd, firstRow, lastRow, col);
            // 商品参数
            for (Sku sku : skuList) {
                int index = col;
                row++;
                // 商品中文名称
                writer.writeCellValue(++index, row, sku.getProdNameCn());
                // 商品英文名称
                writer.writeCellValue(++index, row, prod.getLang() == 1 ? sku.getProdNameEn() : null);
                // 属性名称
                writer.writeCellValue(++index, row, sku.getProperties());
                // 原价
                writer.writeCellValue(++index, row, sku.getOriPrice());
                // 实际价格
                writer.writeCellValue(++index, row, sku.getPrice());
                // 库存
                writer.writeCellValue(++index, row, sku.getStocks());
                // 商家编码
                writer.writeCellValue(++index, row, sku.getPartyCode());
                // 商品重量
                writer.writeCellValue(++index, row, sku.getWeight());
                // 商品体积
                writer.writeCellValue(++index, row, sku.getVolume());
            }
        }
        return row;
    }

    @Override
    public ExcelWriter getProdExcelWriter(Long shopId) {
        //通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getBigWriter();
        // 商品导出or模板
        List<String> headerList = getHeaderList(shopId);
        getSheet(writer, shopId);
        writer.merge(headerList.size() - 1, "商品信息整理");
        writer.writeRow(headerList);
        return writer;
    }

    @Override
    public void downloadModel(HttpServletResponse response, Long shopId) {
        //通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getBigWriter();
        // 商品导出模板
        String[] headerCn = {"商品中文名称（必填*）", "商品英文名称", "商品类型（必填*）", "商品类别（必填*）", "平台分类（必填*）", "店铺分类（必填*）", "商品中文卖点", "商品英文卖点", "配送方式", "运费选择（当商品类别为实物商品且类型为普通商品时必填*）", "固定运费（当运费选择为固定运费时必填*）","商品状态",
                "核销次数(虚拟商品必填*)","多次核销次数(多次核销选项，不填默认无限次)", "核销有效期(有效期，x天，(虚拟商品必填*))", "核销开始时间(格式:2021-10-11 14:32:01，核销有效期是自定义则必填)", "核销结束时间(格式:2021-10-11 14:32:01，核销有效期是自定义则必填)",
                "可以退款(虚拟商品必填*)", "虚拟商品留言 格式是[{\"name\": \"xx\", \"isRequired\": true}]", "商品参数字符串 格式是k1:v1;k2:v2",
                "商品sku中文名称", "商品sku英文名称", "销售属性组合字符串 格式是p1:v1;p2:v2", "原价", "价格", "库存", "商品编码", "商品重量", "商品体积"};
        String[] headerEn = {"Chinese name(Required*)", "English name", "Product Type(Required*)", "Product Category(Required*)", "platform classification(Required*)", "store classification(Required*)", "Chinese selling point",
                "English selling point", "delivery method", "Freight selection(Required when the product category is physical goods and the product type is general goods*)","Shipping costs(Required when shipping selected as fixed*)", "product status",
                "Checkout Count(Virtual goods required*)","The number of multiple write-offs (multiple write-off options, do not fill in the default unlimited)", "Write-off validity(Valid period, x days(Virtual goods required*))", "Checkout Start Time(If the validity period of the write-off is customized, it is required)", "Checkout End Time(If the validity period of the write-off is customized, it is required)",
                "Refundable(Virtual goods required*)", "Virtual Goods Message Format is [{\"name\": \"xx\", \"isRequired\": true}]", "Commodity parameter string The format is k1:v1;k2:v2",
                "Chinese name of commodity", "English name of commodity", "sales attribute combination string format is P1: V1; P2: V2", "original price", "price",
                "Inventory", "commodity code", "commodity weight", "commodity volume"};
        List<String> headerList = Arrays.asList(Objects.equals(I18nMessage.getDbLang(), 0) ? headerCn : headerEn);
        Sheet sheet = getSheet(writer, shopId);

        writer.merge(headerList.size() - 1, "商品信息整理");
        writer.writeRow(headerList);
        try (Workbook workbook = writer.getWorkbook()) {
            dropDownList(shopId, sheet, workbook);
            PoiExcelUtil.writeExcel(response, writer);
        } catch (Exception e) {
            log.error("Exception:", e);
        }
    }

    @Override
    public Object parseFile(MultipartFile file, Long shopId, Long employeeId, List<Long> prodIds, boolean isSameCity) throws Exception {
        Integer lang = I18nMessage.getDbLang();
        try (InputStream inputStream = file.getInputStream();
             Workbook workBook = WorkbookFactory.create(inputStream)) {
            //读取工作表
            Sheet sheet = workBook.getSheetAt(0);
            int rowNumber = sheet.getPhysicalNumberOfRows();
            //校验是否填写内容
            if (rowNumber <= Constant.EXCEL_BEGIN_ROW) {
                // 文件无内容
                throw new YamiShopBindException("yami.document.no.content");
            }
            // * 2.1不是Excel文件，直接返回提示信息
            if (!(file.getOriginalFilename().contains(Constant.XLS_FILE) || file.getOriginalFilename().contains(Constant.XLSX_FILE))) {
                // 格式错误
                throw new YamiShopBindException("yami.file.error");
            }

            // 获取平台分类map
            List<CategoryShopVO> categoryShopList = categoryShopService.listSigningCategoryByShopId(shopId, LanguageEnum.LANGUAGE_ZH_CN.getLang());
            // 如果重复的key取最后一个的值
            categoryShopList = categoryShopList.stream().filter(item -> Objects.equals(item.getStatus(), SigningStatus.SUCCESS.value())).collect(Collectors.toList());
            if (CollUtil.isEmpty(categoryShopList)) {
                // 当前店铺没有签约的平台分类，请先签约分类
                throw new YamiShopBindException("yami.shop.not.category");
            }
            Map<String, Long> categoryMap = categoryShopList.stream().collect(Collectors.toMap(CategoryShopVO::getName, CategoryShopVO::getCategoryId, (key1, key2) -> key2));
            // 获取店铺分类map
            List<Category> shopCategories = categoryService.list(new LambdaQueryWrapper<Category>().eq(Category::getShopId, shopId).eq(Category::getStatus, ProdStatusEnums.NORMAL.getValue()));
            // 如果重复的key取最后一个的值
            Map<String, Long> shopCategoryMap = shopCategories.stream().collect(Collectors.toMap(Category::getCategoryName, Category::getCategoryId, (key1, key2) -> key2));
            // 获取运费模板map
            List<TransportParam> transportParams = new ArrayList<>();
            applicationContext.publishEvent(new GetTransportNamesEvent(transportParams, shopId));
            Map<String, Long> transportMap = transportParams.stream().collect(Collectors.toMap(TransportParam::getTransName, TransportParam::getTransportId, (key1, key2) -> key2));
            transportMap.put(DeliveryTemplateType.FREE_SHIPPING.getName(), DeliveryTemplateType.FREE_SHIPPING.getValue());
            transportMap.put(DeliveryTemplateType.FREIGHT.getName(), DeliveryTemplateType.FREIGHT.getValue());

            //获取已有的，未被删除的商品编码
            List<Product> dbProducts = productMapper.selectList(new LambdaQueryWrapper<Product>().eq(Product::getShopId, shopId)
                    .ne(Product::getStatus, -1));
            List<Long> dbProdIds = dbProducts.stream().map(Product::getProdId).collect(Collectors.toList());
            List<String> partyCodes = skuService.listSkuByProdIds(dbProdIds, null);
            HashSet<String> partyCodeSet = new HashSet<>();
            // 获取商品审核开关
            SysProdConfig sysProdConfig = sysConfigService.getSysConfigObject(Constant.PROD_SWITCH_CONFIG, SysProdConfig.class);
            Boolean prodAudit = sysProdConfig.getProdAudit();
            List<ProductParam> products = new ArrayList<>();
            List<String> errorList = new ArrayList<>();
            Set<Integer> errorSet = new HashSet<>();
            List<Sku> skuList = new ArrayList<>();
            int totalRows = (rowNumber - Constant.EXCEL_BEGIN_ROW);
            int insertRows = loadProdData(shopId, isSameCity, sheet, rowNumber, categoryMap, shopCategoryMap, transportMap, partyCodes, partyCodeSet, prodAudit, products, errorList, errorSet, skuList);
            String response;
            //过滤删除状态的商品
            products = products.stream().filter(prod -> !prod.getStatus().equals(ProdStatusEnums.DELETE.getValue())).collect(Collectors.toList());
            //不为空批量导
            if (CollectionUtils.isNotEmpty(products)) {
                int errorCount = errorList.size();
                saveImportProd(prodIds, products, errorList, errorSet, employeeId);
                errorCount = errorList.size() - errorCount;
                response = "检查到" + totalRows + "条商品，成功导入" +
                        (products.size() - errorCount) + "条商品信息！错误" + errorSet.size() + "条商品信息！" +
                        (CollUtil.isNotEmpty(errorList) ? errorList.toString() : "");
                return response;
            }
            response = Objects.equals(lang, LanguageEnum.LANGUAGE_ZH_CN.getLang()) ? "数据错误!导入0条" : "Data error! Import 0 items";
            if (CollUtil.isNotEmpty(errorList)) {
                response = response + errorList.toString();
            }
            return response;
        } catch (Exception e) {
            return e.getMessage();
        }
    }

    @Override
    public void prodEffectExport(HttpServletResponse response, ProdEffectParam param, Integer lang) {

        //通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getBigWriter();
        // 导出模板
        String[] header = {"店铺名称", "商品名称", "商品价格", "商品状态", "曝光次数", "曝光人数", "加购人数", "加购件数", "下单人数", "支付人数", "下单件数", "支付件数",
                "下单金额", "支付金额", "单品转化率", "申请退款订单数", "申请退款人数", "成功退款订单数", "成功退款人数", "成功退款金额", "退款率"};
        List<String> headerList = Arrays.asList(header);
        Sheet sheet = writer.getSheet();
        sheet.setColumnWidth(0, 40 * 256);
        sheet.setColumnWidth(1, 40 * 256);
        sheet.setColumnWidth(2, 40 * 256);
        for (int i = 3; i <= headerList.size(); i++) {
            sheet.setColumnWidth(i, 30 * 256);
        }

        writer.merge(headerList.size() - 1, "商品洞察");
        writer.writeRow(headerList);
        if (Objects.equals(1, param.getGroup())) {
            return;
        }
        IPage<ProdEffectRespParam> pageProdEffect = productService.pageProdEffect(new PageParam<>(), param, I18nMessage.getDbLang(), true);
        if (CollUtil.isEmpty(pageProdEffect.getRecords())) {
            PoiExcelUtil.writeExcel(response, writer);
            return;
        }
        try {
            int row = 1;
            // 写入商品洞察信息
            for (ProdEffectRespParam prod : pageProdEffect.getRecords()) {
                int col = -1;
                PoiExcelUtil.mergeIfNeed(writer, ++row, row, ++col, col, prod.getShopName());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getProdName());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getPrice());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getProdStatus());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getExpose());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getExposePersonNum());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getAddCartPerson());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getAddCart());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getPlaceOrderPerson());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getPayPerson());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getPlaceOrderNum());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getPayNum());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getPlaceOrderAmount());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getPayAmount());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getSingleProdRate());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getRefundNum());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getRefundPerson());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getRefundSuccessNum());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getRefundSuccessPerson());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getRefundSuccessAmount());
                PoiExcelUtil.mergeIfNeed(writer, row, row, ++col, col, prod.getRefundSuccessRate());
            }
            PoiExcelUtil.writeExcel(response, writer);
        } catch (Exception e) {
            log.error("商品洞察导出:", e);
            throw new YamiShopBindException(e.getMessage());
        }
    }

    @Override
    public void dropDownList(Long shopId, Sheet sheet, Workbook workbook) {
        int prodTypeIndex = 2;
        int moldIndex = 3;
        int categoryIndex = 4;
        int dvyTypeIndex = 8;
        int statusIndex = 11;
        int writeOffTypeIndex = 12;
        int isRefundIndex = 17;

        // shopId: null-导出商城的商品,比店铺模板多了一个店铺名称，所以对应属性的索引要+1
        if (Objects.isNull(shopId)) {
            prodTypeIndex++;
            moldIndex++;
            dvyTypeIndex++;
            statusIndex++;
            writeOffTypeIndex++;
            isRefundIndex++;
        }


        if (Objects.isNull(shopId)) {
            shopId = Constant.PLATFORM_SHOP_ID;
        }
        // 限制类型
        String[] prodType = {"普通商品", "活动商品"};
        PoiExcelUtil.createDropDownList(sheet, prodType, 2, 50000, prodTypeIndex, prodTypeIndex);
        // 限制类别
        String[] prodCategory = {"实物商品", "虚拟商品"};
        PoiExcelUtil.createDropDownList(sheet, prodCategory, 2, 50000, moldIndex, moldIndex);

        // 限制商家分类和平台分类
        List<CategoryShopVO> categoryShopList = categoryShopService.listSigningCategoryByShopId(shopId, LanguageEnum.LANGUAGE_ZH_CN.getLang());
        categoryShopList = categoryShopList.stream().filter(item -> Objects.equals(item.getStatus(), SigningStatus.SUCCESS.value())).collect(Collectors.toList());

        if (CollectionUtils.isNotEmpty(categoryShopList)) {
            String[] categoryName = new String[categoryShopList.size()];
            for (int i = 0; i < categoryShopList.size(); i++) {
                categoryName[i] = categoryShopList.get(i).getName();
            }
            PoiExcelUtil.createDropDownList(workbook, sheet, Arrays.asList(categoryName), 2, 50000, categoryIndex, categoryIndex);
        }
        List<Category> shopCategories = categoryService.list(new LambdaQueryWrapper<Category>().eq(Category::getShopId, shopId).eq(Category::getStatus, ProdStatusEnums.NORMAL.getValue()));
        if (CollectionUtils.isNotEmpty(shopCategories)) {
            String[] categoryName = new String[shopCategories.size()];
            for (int i = 0; i < shopCategories.size(); i++) {
                categoryName[i] = shopCategories.get(i).getCategoryName();
            }
            PoiExcelUtil.createDropDownList(workbook, sheet, Arrays.asList(categoryName), 2, 50000, 5, 5);
        }

        // 限制物流方式
        String[] dvyTypes = {"用户自提", "同城配送", "用户自提且同城配送"};
        PoiExcelUtil.createDropDownList(sheet, dvyTypes, 2, 50000, dvyTypeIndex, dvyTypeIndex);

        // 组装运费模板
        List<TransportParam> transportParams = new ArrayList<>();
        applicationContext.publishEvent(new GetTransportNamesEvent(transportParams, shopId));
        List<String> transportNameList = new ArrayList<>();
        transportNameList.add(DeliveryTemplateType.FREE_SHIPPING.getName());
        transportNameList.add(DeliveryTemplateType.FREIGHT.getName());
        if (CollectionUtils.isNotEmpty(transportParams)) {
            transportParams.forEach(transportParam -> transportNameList.add(transportParam.getTransName()));
        }
        PoiExcelUtil.createDropDownList(workbook, sheet, transportNameList, 2, 50000, 9, 9);

        String[] statusStr = {"下架", "上架"};
        PoiExcelUtil.createDropDownList(sheet, statusStr, 2, 50000, statusIndex, statusIndex);

        // 限制核销次数 -1.多次核销 0.无需核销 1.单次核销
        String[] writeOffType = {"多次核销", "无需核销", "单次核销"};
        PoiExcelUtil.createDropDownList(sheet, writeOffType, 2, 50000, writeOffTypeIndex, writeOffTypeIndex);

        // 核销有效期 -1.长期有效 0.自定义 x.x天内有效
//        String[] writeOffTime = {"长期有效","自定义","x天内有效"};
//        createDropDownList(sheet,writeOffTime,2,50000,11,11);
        // 是否可以退款 1.可以 0.不可以
        String[] isRefund = {"是", "否"};
        PoiExcelUtil.createDropDownList(sheet, isRefund, 2, 50000, isRefundIndex, isRefundIndex);
    }

    public void saveImportProd(List<Long> prodIds, List<ProductParam> products, List<String> errorList, Set<Integer> errorSet, Long employeeId) {
        int count = 0;
        for (ProductParam productParam : products) {
            count++;
            try {
                // 有事务，发生错误会回滚
                productParam.setEmployeeId(employeeId);
                productService.saveProduct(productParam);
                prodIds.add(productParam.getProdId());
            } catch (Exception e) {
                //错误信息提示
                errorList.add("第" + count + "行数据有错误:" + e.getMessage());
                errorSet.add(count);
            }
        }
    }

    private int loadProdData(Long shopId, boolean isSameCity, Sheet sheet, int rowNumber, Map<String, Long> categoryMap, Map<String, Long> shopCategoryMap, Map<String, Long> transportMap, List<String> partyCodes, HashSet<String> partyCodeSet, Boolean prodAudit, List<ProductParam> products, List<String> errorList, Set<Integer> errorSet, List<Sku> skuList) {
        List<ProdLang> prodLangs;
        Date date = new Date();
        Integer totalStocks = 0;
        double price = 0.0;
        double oriPrice = 0.0;
        int insertRows = 0;
        ProductParam product = new ProductParam();
        //循环读取每一行数据并校验
        for (int i = 2; i < rowNumber; i++) {
            try {
                //读取行
                Row row = sheet.getRow(i);
                int cell = 0;
                String prodName = PoiExcelUtil.getRowValue(row, cell, false);
                String prodNameEn = PoiExcelUtil.getRowValue(row, ++cell, false);
                String prodType = PoiExcelUtil.getRowValue(row, ++cell, false);
                String prodCategory = PoiExcelUtil.getRowValue(row, ++cell, false);
                String category = PoiExcelUtil.getRowValue(row, ++cell, false);
                String shopCategory = PoiExcelUtil.getRowValue(row, ++cell, false);
                String brief = PoiExcelUtil.getRowValue(row, ++cell, false);
                String briefEn = PoiExcelUtil.getRowValue(row, ++cell, false);
                String dvyType = PoiExcelUtil.getRowValue(row, ++cell, false);
                String transportName = PoiExcelUtil.getRowValue(row, ++cell, false);
                String freightAmount = PoiExcelUtil.getRowValue(row, ++cell, true);
                String statusStr = PoiExcelUtil.getRowValue(row, ++cell, false);
                String writeOffNum = PoiExcelUtil.getRowValue(row, ++cell, false);
                String writeOffMultipleCount = PoiExcelUtil.getRowValue(row, ++cell, false);
                String writeOffTime = PoiExcelUtil.getRowValue(row, ++cell, false);
                String writeOffStart = PoiExcelUtil.getRowValue(row, ++cell, false);
                String writeOffend = PoiExcelUtil.getRowValue(row, ++cell, false);
                String isRefund = PoiExcelUtil.getRowValue(row, ++cell, false);
                String virtualRemark = PoiExcelUtil.getRowValue(row, ++cell, false);
                String prodParameterStr = PoiExcelUtil.getRowValue(row, ++cell, false);
                if (StrUtil.isNotBlank(prodName) || !PoiExcelUtil.isMergedRegion(sheet, i, 0)) {
                    if (StrUtil.isBlank(prodName) && StrUtil.isBlank(category) && StrUtil.isBlank(shopCategory)
                            && StrUtil.isBlank(brief) && Objects.isNull(transportMap.get(transportName))) {
                        continue;
                    }
                    if (StrUtil.isBlank(prodName)) {
                        // 中文名称不能为空
                        throw new YamiShopBindException("yami.prod.name.not.blank");
                    }
                    if (StrUtil.isBlank(prodNameEn)) {
                        prodNameEn = prodName;
                    }
                    if (StrUtil.isBlank(prodType)) {
                        prodType = ProdType.PROD_TYPE_NORMAL.desc();
                    }
                    if (StrUtil.isBlank(prodCategory)) {
                        // 商品类别不能为空
                        throw new YamiShopBindException("商品类别不能为空");
                    }
                    skuList = new ArrayList<>();
                    product = new ProductParam();
                    prodLangs = new ArrayList<>();
                    newProdInfo(shopId, isSameCity, categoryMap, shopCategoryMap, transportMap, prodAudit, products, prodLangs, date, product, prodName, prodNameEn, prodType, prodCategory, category, shopCategory, brief, briefEn, dvyType, transportName,freightAmount, statusStr, writeOffNum, writeOffMultipleCount, writeOffTime, writeOffStart, writeOffend, isRefund, virtualRemark, prodParameterStr);
                    totalStocks = 0;
                    oriPrice = 0.0;
                    price = 0.0;
                    insertRows++;
                }
                String skuProdNameCn = PoiExcelUtil.getRowValue(row, ++cell, false);
                String skuProdNameEn = PoiExcelUtil.getRowValue(row, ++cell, false);
                String properties = PoiExcelUtil.getRowValue(row, ++cell, false);
                double skuOriPrice = Double.valueOf(PoiExcelUtil.getRowValue(row, ++cell, true));
                //判断规格名是否重复
                String checkProperties = checkProperties(properties, product);
                if (Objects.isNull(skuOriPrice) || skuOriPrice < Constant.MIN_PRODUCT_AMOUNT) {
                    skuOriPrice = Constant.MIN_PRODUCT_AMOUNT;
                }
                double skuPrice = Double.valueOf(PoiExcelUtil.getRowValue(row, ++cell, true));
                if (Objects.isNull(skuPrice) || skuPrice < Constant.MIN_PRODUCT_AMOUNT) {
                    skuPrice = Constant.MIN_PRODUCT_AMOUNT;
                }
                // 跳过积分价格的字段 --> 改：Excel没有该列
                Integer stocks = Double.valueOf(PoiExcelUtil.getRowValue(row, ++cell, true)).intValue();
                String partyCode = PoiExcelUtil.getRowValue(row, ++cell, false);
                if (StrUtil.isBlank(partyCode) || partyCodes.contains(partyCode) || partyCodeSet.contains(partyCode)) {
                    partyCode = StringUtils.join("RM", String.valueOf(snowflake.nextId()));
                }
                partyCodeSet.add(partyCode);
                double weight = Double.valueOf(PoiExcelUtil.getRowValue(row, ++cell, true));
                double volume = Double.valueOf(PoiExcelUtil.getRowValue(row, ++cell, true));
                oriPrice = Objects.equals(oriPrice, 0.0) ? skuOriPrice : oriPrice;
                price = Objects.equals(price, 0.0) ? skuPrice : price;
                Sku sku = getSku(date, prodName, prodNameEn, skuProdNameCn, skuProdNameEn, checkProperties, skuOriPrice, skuPrice, stocks, partyCode, weight, volume);
                skuList.add(sku);
                // 如果prodName为空并且为合并行则为一个prod
                totalStocks += stocks;
                if (price > skuPrice) {
                    oriPrice = skuOriPrice;
                    price = skuPrice;
                }
                product.setOriPrice(oriPrice);
                product.setScorePrice(0);
                product.setPrice(price);
                product.setTotalStocks(totalStocks);
                product.setSkuList(skuList);
            } catch (NumberFormatException numberFormatException) {
                errorList.add("第" + (i + 1) + "行数据有错误：" + "输入内容[" + numberFormatException.getMessage().substring(18) + "]格式错误");
                errorSet.add(i + 1);
                product.setStatus(ProdStatusEnums.DELETE.getValue());
            } catch (Exception e) {
                //错误信息提示
                errorList.add("第" + (i + 1) + "行数据有错误：" + e.getMessage());
                errorSet.add(i + 1);
                product.setStatus(ProdStatusEnums.DELETE.getValue());
            }
        }
        List<ProductParam> wrongList = products.stream().filter(prod -> CollUtil.isNotEmpty(prod.getSkuList()) && prod.getSkuList().size() > 100).collect(Collectors.toList());
        if (CollUtil.isNotEmpty(wrongList)) {
            // 检测到存在超过100组规格的商品，请修改后重新导入
            throw new YamiShopBindException("yami.exprot.prod.sku.error");
        }
        return insertRows;
    }

    public String checkProperties(String properties, ProductParam product) {
        if (StrUtil.isBlank(properties)) {
            return properties;
        }
        StringBuilder stringBuilder = new StringBuilder();
        if (!hasSemicolon(properties)) {
            if (properties.split(COLON).length == 0) {
                return "";
            }
            if (properties.split(COLON).length != VALID_NUM) {
                product.setStatus(-1);
                //销售属性组合字符串格式错误
                String message = I18nMessage.getMessage("yami.properties");
                String error = I18nMessage.getMessage("yami.properties.error");
                throw new YamiShopBindException(message + "[" + properties + "]" + error);
            }
            return stringBuilder.append(properties).toString();
        }
        Set<String> propertyKeySet = new HashSet<>();
        String[] property = properties.split(SEMICOLON);
        for (int i = 0; i < property.length; i++) {
            String prop = property[i];
            if (prop.split(COLON).length != VALID_NUM) {
                product.setStatus(-1);
                //销售属性组合字符串格式错误
                String message = I18nMessage.getMessage("yami.properties");
                String error = I18nMessage.getMessage("yami.properties.error");
                throw new YamiShopBindException(message + "[" + properties + "]" + error);
            }
            String key = prop.split(COLON)[0].trim();
            String value = prop.split(COLON)[1].trim();
            if (propertyKeySet.contains(key)) {
                product.setStatus(-1);
                //规格名重复
                String message = I18nMessage.getMessage("yami.sku.name");
                String error = I18nMessage.getMessage("yami.is.duplicate");
                throw new YamiShopBindException(message + "[" + key + "]" + error);
            }
            if (StrUtil.isNotBlank(key)) {
                propertyKeySet.add(key);
            }
            if (StrUtil.isNotBlank(value) && i != property.length - 1) {
                stringBuilder.append(prop).append(";");
            } else if (i == property.length - 1) {
                stringBuilder.append(prop);
            }
        }
        if (propertyKeySet.isEmpty()) {
            return "";
        }
        return stringBuilder.toString();
    }

    private Sku getSku(Date date, String prodName, String prodNameEn, String skuProdNameCn, String skuProdNameEn, String properties, double skuOriPrice, double skuPrice, Integer stocks, String partyCode, double weight, double volume) {
        Sku sku = new Sku();
        sku.setSkuName(skuProdNameCn);
        sku.setSkuNameEn(skuProdNameEn);
        sku.setProdNameCn(prodName);
        sku.setProdNameEn(prodNameEn);
        sku.setProperties(properties);
        sku.setPropertiesEn(properties);
        // 没有规格时，不需要插入sku名称
        if (StrUtil.isBlank(properties)) {
            sku.setSkuName("");
            sku.setSkuNameEn("");
        }
        sku.setOriPrice(skuOriPrice);
        sku.setPrice(skuPrice);
        sku.setStocks(Math.abs(stocks));
        sku.setSkuScore(0L);
        sku.setPartyCode(partyCode);
        sku.setVolume(Math.abs(volume));
        sku.setUpdateTime(date);
        sku.setWeight(Math.abs(weight));
        sku.setVersion(1);
        sku.setIsDelete(0);
        sku.setStatus(1);
        return sku;
    }

    private void newProdInfo(Long shopId, boolean isSameCity, Map<String, Long> categoryMap, Map<String, Long> shopCategoryMap, Map<String, Long> transportMap, Boolean prodAudit, List<ProductParam> products, List<ProdLang> prodLangs, Date date, ProductParam product, String prodName, String prodNameEn, String prodType, String prodCategory, String category, String shopCategory, String brief, String briefEn, String dvyType, String transportName,String freightAmount, String statusStr, String writeOffNum, String writeOffMultipleCount, String writeOffTime, String writeOffStart, String writeOffend, String isRefund, String virtualRemark, String prodParameterStr) {
        if (StrUtil.isBlank(category)) {
            // 平台分类不能为空
            throw new YamiShopBindException("yami.platform.category.not.blank");
        }
        if (StrUtil.isBlank(shopCategory)) {
            // 店铺分类不能为空
            throw new YamiShopBindException("yami.prod.shop.category.not.blank");
        }
        // 保存语言信息
        product.setProdNameCn(prodName);
        product.setProdNameEn(prodNameEn);
        product.setBriefCn(brief);
        product.setBriefEn(briefEn);

        product.setShopId(shopId);
        product.setProdName(prodName);
        product.setCategoryId(categoryMap.get(category));
        if (Objects.isNull(product.getCategoryId())) {
            throw new YamiShopBindException("分类" + category + "的签约关系已失效，无法使用该分类创建商品");
        }
        product.setShopCategoryId(shopCategoryMap.get(shopCategory));
        product.setBrief(brief);
        product.setDeliveryModeVo(loadDvyType(dvyType, isSameCity));
        product.setDeliveryTemplateId(transportMap.get(transportName));
        if (Objects.equals(transportMap.get(transportName), DeliveryTemplateType.FREIGHT.getValue())) {
            double deliveryAmount = Double.parseDouble(freightAmount);
            if (deliveryAmount < Constant.MIN_PRODUCT_AMOUNT) {
                deliveryAmount = Constant.MIN_PRODUCT_AMOUNT;
            }
            product.setDeliveryAmount(deliveryAmount);
        }
        product.setStatus(Objects.equals(statusStr, "上架") ? ProdStatusEnums.NORMAL.getValue() : ProdStatusEnums.SHOP_OFFLINE.getValue());
        // 如果商品审核true，且是商品上架状态的则为待审核状态
        if (BooleanUtil.isTrue(prodAudit) && Objects.equals(product.getStatus(), ProdStatusEnums.NORMAL.getValue())) {
            product.setStatus(ProdStatusEnums.AUDIT.getValue());
        }
        if (StrUtil.equals("实物商品", prodCategory)) {
            if (StrUtil.equals(ProdType.PROD_TYPE_NORMAL.desc(), prodType)) {
                if (Objects.isNull(transportMap.get(transportName))) {
                    // 运费模板不存在
                    throw new YamiShopBindException("yami.prod.transport.not.exist");
                }
                product.setProdType(ProdType.PROD_TYPE_NORMAL.value());
            } else {
                product.setProdType(ProdType.PROD_TYPE_ACTIVE.value());
            }
            product.setMold(0);
        } else {
            // 处理下虚拟商品信息
            handleVirtualProdInfo(product, prodType, writeOffNum, writeOffMultipleCount, writeOffTime, writeOffStart, writeOffend, isRefund, virtualRemark);
        }
        product.setProdParameterList(parseParameter(prodParameterStr, product));
        product.setPic("");
        product.setImgs("");
        product.setContent("<p></p>");
        products.add(product);
    }

    private Product.DeliveryModeVO loadDvyType(String dvyType, Boolean isSameCity) {
        Product.DeliveryModeVO mode = new Product.DeliveryModeVO();
        mode.setHasShopDelivery(true);
        mode.setHasUserPickUp(false);
        mode.setHasCityDelivery(false);
        if (Objects.isNull(dvyType)) {
            return mode;
        }
        if (dvyType.contains("用户自提且同城配送")) {
            mode.setHasUserPickUp(true);
            mode.setHasCityDelivery(BooleanUtil.isTrue(isSameCity));
            return mode;
        }

        if (dvyType.contains("用户自提")) {
            mode.setHasUserPickUp(true);
        }

        if (dvyType.contains("同城配送")) {
            mode.setHasCityDelivery(BooleanUtil.isTrue(isSameCity));
        }
        return mode;
    }

    private void handleVirtualProdInfo(ProductParam product, String prodType, String writeOffNum,String writeOffMultipleCount, String writeOffTime, String writeOffStart, String writeOffEnd, String isRefund, String virtualRemark) {
        if (StrUtil.equals(ProdType.PROD_TYPE_ACTIVE.desc(), prodType)) {
            throw new YamiShopBindException("虚拟商品不能为活动商品");
        }
        if (StrUtil.isBlank(writeOffNum)) {
            throw new YamiShopBindException("虚拟商品的核销次数不能为空");
        }
        String num = StrUtil.equals(writeOffNum, "多次核销") ? "-1" : StrUtil.equals(writeOffNum, "单次核销") ? "1" : "0";
        if (!"0".equals(num) && StrUtil.isBlank(writeOffTime)) {
            throw new YamiShopBindException("虚拟商品的核销有效期不能为空");
        }
        // 多次核销次数
        int count;
        try {
            count = Math.max(Integer.parseInt(writeOffMultipleCount), 2);
        } catch (NumberFormatException e) {
            count = -1;
        }
        if ("-1".equals(num)) {
            product.setWriteOffMultipleCount(count);
        }
        boolean validWriteOffTime = StrUtil.equals(writeOffTime, "自定义") && (StrUtil.isBlank(writeOffStart) || StrUtil.isBlank(writeOffEnd));
        if (validWriteOffTime) {
            throw new YamiShopBindException("虚拟商品的核销有效期不能为空");
        }
        if (StrUtil.isBlank(isRefund)) {
            throw new YamiShopBindException("虚拟商品是否可以退款不能为空");
        }
        if (StrUtil.isBlank(writeOffTime)) {
            product.setWriteOffTime(null);
        } else {
            if(!writeOffTime.equals("当天有效")){
                writeOffTime = writeOffTime.replace("天", "");
            }
            int time = StrUtil.equals(writeOffTime, "长期有效") ? -1 : StrUtil.equals(writeOffTime, "当天有效") ? 1 : StrUtil.equals(writeOffTime, "自定义") ? 0 : Integer.parseInt(writeOffTime);
            product.setWriteOffTime(time);
        }
        product.setWriteOffNum(num);

        if (StrUtil.isNotBlank(writeOffStart)) {
            product.setWriteOffStart(DateUtil.parse(writeOffStart, "yyyy-MM-dd HH:mm:ss"));
        }
        if (StrUtil.isNotBlank(writeOffEnd)) {
            product.setWriteOffEnd(DateUtil.parse(writeOffEnd, "yyyy-MM-dd HH:mm:ss"));
        }
        product.setIsRefund(StrUtil.equals("是", isRefund) ? 1 : 0);
        if(StrUtil.isNotBlank(virtualRemark)){
            if (!JSONValidator.Type.Array.equals(JSONValidator.from(virtualRemark).getType())) {
                throw new YamiShopBindException("yami.prod.import.virtualRemark.notValid");
            }
            List<Map> mapList = JSONObject.parseArray(virtualRemark, Map.class);
            if(mapList.size() > 10){
                List newList = mapList.subList(0, 10);
                virtualRemark = JSON.toJSONString(newList);
            }
        }
        product.setVirtualRemark(StrUtil.isBlank(virtualRemark) ? null : virtualRemark);
        product.setMold(1);
    }

    public List<ProdParameter> parseParameter(String prodParameterStr, ProductParam product) {
        if (StrUtil.isBlank(prodParameterStr)) {
            return null;
        }
        if (!hasSemicolon(prodParameterStr)) {
            int length = prodParameterStr.split(COLON).length;
            if (length == 0) {
                return null;
            }
            if (length != VALID_NUM) {
                throw new YamiShopBindException("yami.prod.parameter.format.error");
            }
        }
        // 商品参数名，值都可以相同，不做校验
        String[] prodParameters = prodParameterStr.split(SEMICOLON);
        List<ProdParameter> prodParameterList = new ArrayList<>();
        String key, value;
        for (String prodParameter : prodParameters) {
            if (prodParameter.split(COLON).length != VALID_NUM) {
                throw new YamiShopBindException("yami.prod.parameter.format.error");
            }
            key = prodParameter.split(COLON)[0].trim();
            value = prodParameter.split(COLON)[1].trim();
            // 非空校验
            if (StrUtil.isBlank(key) || StrUtil.isBlank(value)) {
                throw new YamiShopBindException("yami.prod.parameter.format.error");
            }
            if (key.length() > 10) {
                key = key.substring(0, 10);
            }
            if (value.length() > 20) {
                value = value.substring(0, 20);
            }
            ProdParameter dbParameter = new ProdParameter();
            dbParameter.setProdId(product.getProdId());
            dbParameter.setParameterKey(key);
            dbParameter.setParameterValue(value);
            dbParameter.setParameterKeyEn(key);
            dbParameter.setParameterValueEn(value);
            prodParameterList.add(dbParameter);
        }
        return prodParameterList;
    }

    public boolean hasSemicolon(String str) {
        return Objects.nonNull(str) && !str.isEmpty() && str.contains(SEMICOLON);
    }


    private int writerProdData(ExcelWriter writer, Long shopId, ProductExportParam prod, ProductExportParam esProd, int firstRow, int lastRow, int col) {
        // shopId: null-导出商城的商品,跟具体店铺无关
        if (Objects.isNull(shopId)) {
            // 店铺名称
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, esProd.getShopName());
        }
        // 中文名称
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, esProd.getProdNameZh());
        // 英文名称
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, prod.getLang() == 1 ? esProd.getProdNameEn() : null);
        // 类型
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, ProdType.instance(esProd.getProdType()).desc());
        // 类别
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, Objects.equals(prod.getMold(), 1) ? "虚拟商品" : "实物商品");
        // 平台分类
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, prod.getCategoryName());
        // 店铺分类
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, prod.getShopCategoryName());
        // "卖点"
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, esProd.getBriefZh());
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, prod.getLang() == 1 ? esProd.getBriefEn() : null);
        Product.DeliveryModeVO deliveryModeVO = Json.parseObject(prod.getDeliveryMode(), Product.DeliveryModeVO.class);
        StringBuilder deliveryMode = new StringBuilder();
        if (deliveryModeVO != null && !Objects.equals(prod.getMold(), 1)) {
            if (deliveryModeVO.getHasCityDelivery() != null && deliveryModeVO.getHasCityDelivery()) {
                deliveryMode.append("同城配送，");
            }
            if (deliveryModeVO.getHasUserPickUp() != null && deliveryModeVO.getHasUserPickUp()) {
                deliveryMode.append("用户自提，");
            }
            if (deliveryModeVO.getHasShopDelivery() != null && deliveryModeVO.getHasShopDelivery()) {
                deliveryMode.append("快递配送，");
            }
            deliveryMode.deleteCharAt(deliveryMode.length() - 1);
        } else {
            deliveryMode.append("无需快递");
        }
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, deliveryMode);

        // 运费设置
        String transName = prod.getTransName();
        if(Objects.nonNull(prod.getDeliveryTemplateId()) && prod.getDeliveryTemplateId() <= 0) {
            if (Objects.equals(DeliveryTemplateType.FREE_SHIPPING.getValue(), prod.getDeliveryTemplateId())) {
                transName = DeliveryTemplateType.FREE_SHIPPING.getName();
            } else if (Objects.equals(DeliveryTemplateType.FREIGHT.getValue(), prod.getDeliveryTemplateId())) {
                transName = DeliveryTemplateType.FREIGHT.getName();
            }
        }
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, Objects.equals(prod.getMold(), 1) ? "" : transName);
        // 统一运费
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, Objects.equals(prod.getMold(), 1) ? "" : prod.getDeliveryAmount());
        // 状态
        if (Objects.equals(esProd.getStatus(), ProdStatusEnums.SHOP_OFFLINE.getValue())) {
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, "下架");
        } else if (Objects.equals(esProd.getStatus(), ProdStatusEnums.PLATFORM_OFFLINE.getValue())) {
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, "违规下架");
        } else {
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, ProdStatusEnums.instance(esProd.getStatus()).getDesc());
        }
        // 核销次数
        String numStr = "";
        if (Objects.equals(prod.getMold(), 1) && Objects.nonNull(prod.getWriteOffNum())) {
            numStr = prod.getWriteOffNum() == -1 ? "多次核销" : prod.getWriteOffNum() == 0 ? "无需核销" : "单次核销";
        }
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, numStr);
        // 多次核销次数
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, Objects.equals(prod.getWriteOffMultipleCount(), -1) ? "无限次" : prod.getWriteOffMultipleCount());
        // 核销有效期
        String timeStr = "";
        if (Objects.equals(prod.getMold(), 1) && Objects.nonNull(prod.getWriteOffTime())) {
            timeStr = prod.getWriteOffTime() == -1 ? "长期有效" : prod.getWriteOffTime() == 0 ? "自定义" : prod.getWriteOffTime() + "天";
        }
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, timeStr);
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, prod.getWriteOffStart());
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, prod.getWriteOffEnd());
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,
                Objects.nonNull(prod.getIsRefund()) && prod.getIsRefund() == 0 ? "否" : "是");
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, prod.getVirtualRemark());
        PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, transParameter(prod.getProdParameterList()));
        return col;
    }

    public String transParameter(List<ProdParameter> prodParameterList) {
        if (CollUtil.isEmpty(prodParameterList)) {
            return "";
        }
        StringBuilder builder = new StringBuilder();
        for (int i = 0; i < prodParameterList.size(); i++) {
            ProdParameter prodParameter = prodParameterList.get(i);
            builder.append(prodParameter.getParameterKey()).append(":").append(prodParameter.getParameterValue());
            if (i != prodParameterList.size() - 1) {
                builder.append(";");
            }
        }
        return builder.toString();
    }
    private List<String> getHeaderList(Long shopId) {
        List<String> headerList;
        if (Objects.nonNull(shopId)) {
            String[] headerCn = {"商品中文名称", "商品英文名称", "商品类型", "商品类别", "平台分类", "店铺分类", "商品中文卖点", "商品英文卖点", "配送方式", "运费选择","固定运费", "商品状态",
                    "核销次数","多次核销次数", "核销有效期(有效期，x天)", "核销开始时间(格式:2021-08-08 14:32:01，核销有效期是自定义则必填)", "核销结束时间(格式:2021-10-11 14:32:01，核销有效期是自定义则必填)",
                    "可以退款", "虚拟商品留言 格式是[{\"name\": \"xx\", \"isRequired\": true}]", "商品参数字符串 格式是k1:v1;k2:v2",
                    "商品sku中文名称", "商品sku英文名称", "销售属性组合字符串 格式是p1:v1;p2:v2", "原价", "价格", "库存", "商品编码", "商品重量", "商品体积"};
            String[] headerEn = {"Chinese name", "English name", "Product Type", "Product Category", "platform classification", "store classification", "Chinese selling point",
                    "English selling point", "delivery method", "Freight selection", "Shipping Fee","product status",
                    "Checkout Count","Number of multiple write-offs", "Write-off validity(Valid period, x days)", "Checkout Start Time(Format:2021-10-11 14:32:01, if the validity period of the write-off is custom, it is required)",
                    "Checkout End Time(Format:2021-10-11 14:32:01, if the validity period of the write-off is custom, it is required)",
                    "Refundable", "Virtual Goods Message Format is [{\"name\": \"xx\", \"isRequired\": true}]", "Commodity parameter string The format is k1:v1;k2:v2",
                    "Chinese name of commodity", "English name of commodity", "sales attribute combination string format is P1: V1; P2: V2", "original price", "price",
                    "Inventory", "commodity code", "commodity weight", "commodity volume"};
            headerList = Arrays.asList(Objects.equals(I18nMessage.getDbLang(), 0) ? headerCn : headerEn);
        }
        // shopId: null-导出商城的商品,跟具体店铺无关
        else {
            String[] headerCn = {"店铺名称", "商品中文名称", "商品英文名称", "商品类型", "商品类别", "平台分类", "店铺分类", "商品中文卖点", "商品英文卖点", "配送方式", "运费选择","固定运费", "商品状态",
                    "核销次数","多次核销次数", "核销有效期(有效期，x天)", "核销开始时间(格式:2021-08-08 14:32:01，核销有效期是自定义则必填)", "核销结束时间(格式:2021-10-11 14:32:01，核销有效期是自定义则必填)",
                    "可以退款", "虚拟商品留言 格式是[{\"name\": \"xx\", \"isRequired\": true}]", "商品参数字符串 格式是k1:v1;k2:v2",
                    "商品sku中文名称", "商品sku英文名称", "销售属性组合字符串 格式是p1:v1;p2:v2", "原价", "价格", "库存", "商品编码", "商品重量", "商品体积"};
            String[] headerEn = {"Store name", "Chinese name", "English name", "Product Type", "Product Category", "platform classification", "store classification", "Chinese selling point",
                    "English selling point", "delivery method", "Freight selection", "Shipping Fee","product status",
                    "Checkout Count","Number of multiple write-offs ", "Write-off validity(Valid period, x days)", "Checkout Start Time(Format:2021-10-11 14:32:01, if the validity period of the write-off is custom, it is required)",
                    "Checkout End Time(Format:2021-10-11 14:32:01, if the validity period of the write-off is custom, it is required)",
                    "Refundable", "Virtual Goods Message Format is [{\"name\": \"xx\", \"isRequired\": true}]", "Commodity parameter string The format is k1:v1;k2:v2",
                    "Chinese name of commodity", "English name of commodity", "sales attribute combination string format is P1: V1; P2: V2", "original price", "price",
                    "Inventory", "commodity code", "commodity weight", "commodity volume"};
            headerList = Arrays.asList(Objects.equals(I18nMessage.getDbLang(), 0) ? headerCn : headerEn);
        }
        return headerList;
    }

    private Sheet getSheet(ExcelWriter writer, Long shopId) {
        Sheet sheet = writer.getSheet();
        if (Objects.nonNull(shopId)) {

            sheet.setColumnWidth(0, 60 * 256);
            sheet.setColumnWidth(1, 60 * 256);
            for (int i = 2; i <= 7; i++) {
                sheet.setColumnWidth(i, 20 * 256);
            }
            sheet.setColumnWidth(9, 40 * 256);
            sheet.setColumnWidth(10, 20 * 256);
            for (int i = 12; i <= 19; i++) {
                sheet.setColumnWidth(i, 40 * 256);
            }
            sheet.setColumnWidth(20, 20 * 256);
            sheet.setColumnWidth(21, 20 * 256);
            sheet.setColumnWidth(22, 40 * 256);
        }
        // 店铺id为null，导出整个商城的商品
        else {
            // 平台端加了这一行，显示店铺名称
            sheet.setColumnWidth(0, 40 * 256);
            sheet.setColumnWidth(1, 60 * 256);
            sheet.setColumnWidth(2, 60 * 256);
            for (int i = 3; i <= 8; i++) {
                sheet.setColumnWidth(i, 20 * 256);
            }
            sheet.setColumnWidth(10, 40 * 256);
            for (int i = 13; i <= 20; i++) {
                sheet.setColumnWidth(i, 40 * 256);
            }
            sheet.setColumnWidth(21, 20 * 256);
            sheet.setColumnWidth(22, 20 * 256);
            sheet.setColumnWidth(23, 40 * 256);
        }
        return sheet;
    }
}
